package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import bean.secretarioBean;

public class secretarioDao {

	public void inserir(Connection conn, secretarioBean secretario)
			throws SQLException {

		String sql = ("select max(id) from os");
		PreparedStatement ps = conn.prepareStatement(sql);

		ResultSet rs = ps.executeQuery();

		if (rs.next()) {
			secretario.setIdos(rs.getInt("max"));
		}
		ps.close();
		rs.close();

		String sql2 = "insert into secretario(idsecretario, idos) values(?,?)";

		PreparedStatement ps2 = conn.prepareStatement(sql2);
		ps2.setInt(1, secretario.getIdsecretario());
		ps2.setInt(2, secretario.getIdos());
		ps2.execute();
		ps2.close();
	}

	public void alterar(Connection conn, secretarioBean secretario)
			throws SQLException {

		String sql = "update secretario set idsecretario = ? where idos = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, secretario.getIdsecretario());
		ps.setInt(2, secretario.getIdos());
		ps.execute();
		ps.close();
	}

	public void remover(Connection conn, secretarioBean secretario)
			throws SQLException {

		String sql = "delete from secretario where id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, secretario.getId());
		ps.execute();
		ps.close();
	}

	public ArrayList<secretarioBean> getLista(Connection conn)
			throws SQLException {

		String sql = "select * from secretario";

		PreparedStatement ps = conn.prepareStatement(sql);

		ResultSet rs = ps.executeQuery();
		ArrayList<secretarioBean> secretarios = new ArrayList<secretarioBean>();
		while (rs.next()) {
			secretarioBean secretario = new secretarioBean();
			secretario.setId(rs.getInt("id"));
			secretario.setIdsecretario(rs.getInt("idsecretario"));
			secretario.setIdos(rs.getInt("idos"));

			secretarios.add(secretario);
		}
		ps.close();
		rs.close();

		return secretarios;

	}

	public secretarioBean getsecretario(Connection conn,
			secretarioBean secretarioBean) throws SQLException {

		String sql = "select * from secretario where id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, secretarioBean.getId());

		ResultSet rs = ps.executeQuery();

		secretarioBean secretario = new secretarioBean();
		if (rs.next()) {
			secretario.setId(rs.getInt("id"));
			secretario.setIdsecretario(rs.getInt("idsecretario"));
			secretario.setIdos(rs.getInt("idos"));
		}
		ps.close();
		rs.close();

		return secretario;

	}

	public secretarioBean getSecretario(Connection conn, secretarioBean secretarioBean)
			throws SQLException {

		String sql = "select os.id as idos, secretario.id as id, secretario.idsecretario as idsecretario, funcionario.nome as nome from os, secretario, funcionario where os.id = ? and os.id = secretario.idos and secretario.idsecretario = funcionario.id";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, secretarioBean.getIdos());

		ResultSet rs = ps.executeQuery();

		secretarioBean secretario = new secretarioBean();
		if (rs.next()) {
			secretario.setId(rs.getInt("id"));
			secretario.setIdsecretario(rs.getInt("idsecretario"));
			secretario.setIdos(rs.getInt("idos"));
			secretario.setNome(rs.getString("nome"));
		}
		ps.close();
		rs.close();

		return secretario;

	}

}
